#!/bin/bash

export HIVE_HOME=/usr/bin/hive

if [ $# == 1 ]

then
   dateStr=$1
   
else
   dateStr=`date -d '-1 day' +'%Y-%m-%d'`
  

fi


yearStr=`date -d ${dateStr} +'%Y'`
month_for_quarter=`date -d ${dateStr} +%-m`
quarterStr=$((($month_for_quarter-1)/3+1))
monthStr=`date -d ${dateStr} +'%m'`
dayStr=`date -d ${dateStr} +'%d'`

echo ${yearStr}
echo ${quarterStr}
echo ${monthStr}
echo ${dayStr}


hiveSql="
	--动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--矢量化查询(批量读取操作)
set hive.vectorized.execution.enabled=true; 

set hive.auto.convert.join=true;
-- smb
set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true; 
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
set hive.optimize.bucketmapjoin.sortedmerge = true;


Insert Into edu_dwb.intended_user_billboard Partition (dt)
Select e1.id
     , e1.customer_id
     , e1.itcast_school_id
     , e1.itcast_subject_id
     , e1.origin_type
     , e1.creator
     , e1.intention_study_type
     , e1.origin_channel

     , e2.area

     , e3.tdepart_id
     , e3.department_id

     , e6.name

     , e4.name

     , e5.name

     , e1.yearinfo
     , e1.quarterinfo
     , e1.monthinfo
     , e1.dayinfo
     , e1.hourinfo
     , e1.start_date As dt


From (select * from edu_dwd.fact_customer_relationship where start_date ='${dayStr}') e1
         Left Join (select * from edu_dwd.fact_customer where start_date ='${dayStr}') e2
                   On e1.customer_id = e2.id And e2.deleted = 'false' And e2.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.dim_employee  where start_date ='${dayStr}') e3
                   On e1.creator = e3.id And e3.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.dim_itcast_school where start_date ='${dayStr}') e4
                   On e1.itcast_school_id = e4.id And e4.deleted = 'false' And e4.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.dim_itcast_subject where start_date ='${dayStr}') e5
                   On e1.itcast_subject_id = e5.id And e5.deleted = 'false' And e5.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.dim_scrm_department where start_date ='${dayStr}') e6
                   On e3.tdepart_id = e6.id And e6.deleted = 'false' And e6.end_date = '9999-99-99'
Where e1.deleted = 'false'
  And e1.end_date = '9999-99-99';


Insert Into Table edu_dwb.sign_user_billboard Partition (dt)
Select
     --客户意向表
    e1.id
     , e1.customer_id
     , e1.itcast_school_id
     , e1.itcast_subject_id
     , e1.origin_type
     , e1.creator
     , e1.signup_state
     , e1.signup_time
     , e1.itcast_clazz_id
     , e1.payment_state
     , e1.payment_time


     --员工表表
     , e5.tdepart_id
     , e5.department_id
     --部门表
     , e6.name
     --报名课程表
     , e3.itcast_school_name
     , e3.itcast_subject_name

     --客户线索表
     , e4.clue_state
     , e4.create_date_time


     --意向用户表
     , e1.yearinfo
     , e1.quarterinfo
     , e1.monthinfo
     , e1.dayinfo
     , e1.hourinfo
     , e1.start_date As dt
From (select * from edu_dwd.fact_customer_relationship where start_date ='${dayStr}') e1
         Left Join (select * from edu_dwd.fact_customer_appeal where start_date ='${dayStr}') e2
                   On e1.id = e2.customer_relationship_first_id And e2.deleted = 'false' And
                      e2.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.fact_itcast_clazz where start_date ='${dayStr}') e3
                   On e1.itcast_clazz_id = e3.id And e3.deleted = 'false' And e3.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.fact_customer_clue where start_date ='${dayStr}') e4
                   On e1.id = e4.customer_relationship_id And e4.deleted = 'false' And e4.end_date = '9999-99-99'
         Left Join (select * from edu_dwd.dim_employee where start_date ='${dayStr}') e5
                   On e1.creator = e5.id
         Left Join (select *from edu_dwd.dim_scrm_department where start_date ='${dayStr}') e6
                   On e5.tdepart_id = e6.id And e6.deleted = 'false' And e6.end_date = '9999-99-99';
"

${HIVE_HOME} -S -e "${hiveSql}"